如何在 Entity Framework 中增加 WITH (NOLOCK) 和 Parameter Sniffing 的處理

TLDR

透過實作 DbCommandInterceptor，可以在 Entity Framework 執行 SQL 前動態注入 WITH (NOLOCK) 提示與 OPTION (OPTIMIZE FOR UNKNOWN)。
WITH (NOLOCK) 可避免讀取時被鎖定阻塞，但應避開 INSERT/UPDATE/DELETE 及精確查詢（如 First()、Single()）情境，以免讀取到未提交資料或影響後續異動。
OPTION (OPTIMIZE FOR UNKNOWN) 可有效緩解 SQL Server 的 Parameter Sniffing 效能問題。
攔截器需透過 DbContextOptionsBuilder 的 AddInterceptors 方法註冊至 EF Core。

WARNING

此實作方式在某些現代架構下可能被視為 Anti-Pattern。建議優先考慮使用 RCSI (Read Committed Snapshot Isolation) 架構，或透過 EF Core 的 TagWith 功能進行更精細的控制。詳細建議請參考：RCSI 簡介與改良版 Entity Framework 鎖定提示攔截器

TIP

本篇的完整可執行範例：CloudyWing/EfCoreSqlHintInterceptorSample。

使用 Interceptor 攔截 SQL 指令

在 Entity Framework Core 中，IDbCommandInterceptor 允許開發者在執行資料庫操作前攔截並修改 DbCommand。這對於需要全域性調整 SQL 語法（如注入效能提示）的場景非常實用。

實作攔截器邏輯

什麼情況下會遇到這個問題：當你需要全域性地為讀取查詢加入 NOLOCK 提示，或解決因參數嗅探（Parameter Sniffing）導致的執行計畫不佳問題時。

透過繼承 DbCommandInterceptor 並覆寫 ReaderExecuting 與 ScalarExecuting 等方法，可以動態修改 CommandText：

csharp

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    // 覆寫執行前的方法
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // 排除異動語句，避免影響資料一致性
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // 排除精確查詢（如 First/Single），避免讀取到未提交的資料
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // 加入 OPTION (OPTIMIZE FOR UNKNOWN) 處理 Parameter Sniffing
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

註冊攔截器

攔截器必須在 DbContext 初始化時註冊。可以在 OnConfiguring 中設定，或在 DI 容器注入時指定：

csharp

// 在 DI 中註冊
services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

處理結果與驗證

透過上述攔截器，當執行 ToList() 等查詢時，SQL 語法會自動被注入提示。

什麼情況下會遇到這個問題：當 EF Core 預設產生的 SQL 語法缺乏效能提示，導致在高併發環境下出現鎖定等待，或是因參數值不同導致 SQL Server 選擇了不佳的執行計畫。

執行結果範例

當執行 context.Tests.ToList() 時，產生的 SQL 將自動補上 WITH (NOLOCK) 與 OPTION：

sql

SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

WARNING

以上處理缺乏實際使用的驗證，請依照自身實際情況調整。

異動歷程

2024-07-18
- 初版文件建立。
2026-05-29
- 補上對應 GitHub 範例專案連結。

如何在 Entity Framework 中增加 WITH (NOLOCK) 和 Parameter Sniffing 的處理 ​

TLDR ​

使用 Interceptor 攔截 SQL 指令 ​

實作攔截器邏輯 ​

註冊攔截器 ​

處理結果與驗證 ​

執行結果範例 ​

異動歷程 ​